﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using eShop.DataAccess.Entity;

namespace eShop.DataAccess
{
    public partial class OrderDAL : BaseDAL
    {
        public static bool Insert(Order order)
        {
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Order_InsertOrUpdate");
            db.AddOutParameter(dbCommand, "Id", DbType.Int32, -1);
            db.AddInParameter(dbCommand, "OrderNumber", DbType.String, order.OrderNumber);
            db.AddInParameter(dbCommand, "OrderDate", DbType.DateTime, order.OrderDate);
            db.AddInParameter(dbCommand, "OrderStatus", DbType.String, order.OrderStatus);
            if (order.UserId.HasValue)
                db.AddInParameter(dbCommand, "UserId", DbType.Int32, order.UserId);
            else
                db.AddInParameter(dbCommand, "UserId", DbType.Int32, DBNull.Value);
            db.AddInParameter(dbCommand, "UserFullName", DbType.String, order.UserFullName);
            db.AddInParameter(dbCommand, "UserEmailAddress", DbType.String, order.UserEmailAddress);
            db.AddInParameter(dbCommand, "UserPhoneNumber", DbType.String, order.UserPhoneNumber);
            db.AddInParameter(dbCommand, "UserInstruction", DbType.String, order.UserInstruction);
            db.AddInParameter(dbCommand, "ShippingName", DbType.String, order.ShippingName);
            db.AddInParameter(dbCommand, "ShippingPhoneNumber", DbType.String, order.ShippingPhoneNumber);
            db.AddInParameter(dbCommand, "ShippingAddressLine1", DbType.String, order.ShippingAddressLine1);
            db.AddInParameter(dbCommand, "ShippingAddressLine2", DbType.String, order.ShippingAddressLine2);
            db.AddInParameter(dbCommand, "ShippingZip", DbType.String, order.ShippingZip);
            db.AddInParameter(dbCommand, "ShippingCity", DbType.String, order.ShippingCity);
            db.AddInParameter(dbCommand, "ShippingCountry", DbType.String, order.ShippingCountry);
            db.AddInParameter(dbCommand, "ShippingCost", DbType.Double, order.ShippingCost);
            db.AddInParameter(dbCommand, "SubTotal", DbType.Double, order.SubTotal);
            db.AddInParameter(dbCommand, "OrderAmount", DbType.Double, order.OrderAmount);
            db.AddInParameter(dbCommand, "ShippingStatus", DbType.String, order.ShippingStatus);
            db.AddInParameter(dbCommand, "PaymentType", DbType.String, order.PaymentType);
            db.AddInParameter(dbCommand, "PaymentTrasectionId", DbType.String, order.PaymentTrasectionId);
            db.AddInParameter(dbCommand, "PaymentStatus", DbType.String, order.PaymentStatus);

            if (db.ExecuteNonQuery(dbCommand) > 0)
            {
                order.Id = Convert.ToInt32(db.GetParameterValue(dbCommand, "@Id"));
                return true;
            }
            return false;
        }

        public static bool Update(Order order)
        {
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Order_InsertOrUpdate");
            db.AddInParameter(dbCommand, "Id", DbType.Int32, order.Id);
            db.AddInParameter(dbCommand, "OrderNumber", DbType.String, order.OrderNumber);
            db.AddInParameter(dbCommand, "OrderDate", DbType.DateTime, order.OrderDate);
            db.AddInParameter(dbCommand, "OrderStatus", DbType.String, order.OrderStatus);
            if (order.UserId.HasValue)
                db.AddInParameter(dbCommand, "UserId", DbType.Int32, order.UserId);
            else
                db.AddInParameter(dbCommand, "UserId", DbType.Int32, DBNull.Value);
            db.AddInParameter(dbCommand, "UserFullName", DbType.String, order.UserFullName);
            db.AddInParameter(dbCommand, "UserEmailAddress", DbType.String, order.UserEmailAddress);
            db.AddInParameter(dbCommand, "UserPhoneNumber", DbType.String, order.UserPhoneNumber);
            db.AddInParameter(dbCommand, "UserInstruction", DbType.String, order.UserInstruction);
            db.AddInParameter(dbCommand, "ShippingName", DbType.String, order.ShippingName);
            db.AddInParameter(dbCommand, "ShippingPhoneNumber", DbType.String, order.ShippingPhoneNumber);
            db.AddInParameter(dbCommand, "ShippingAddressLine1", DbType.String, order.ShippingAddressLine1);
            db.AddInParameter(dbCommand, "ShippingAddressLine2", DbType.String, order.ShippingAddressLine2);
            db.AddInParameter(dbCommand, "ShippingZip", DbType.String, order.ShippingZip);
            db.AddInParameter(dbCommand, "ShippingCity", DbType.String, order.ShippingCity);
            db.AddInParameter(dbCommand, "ShippingCountry", DbType.String, order.ShippingCountry);
            db.AddInParameter(dbCommand, "ShippingCost", DbType.Double, order.ShippingCost);
            db.AddInParameter(dbCommand, "SubTotal", DbType.Double, order.SubTotal);
            db.AddInParameter(dbCommand, "OrderAmount", DbType.Double, order.OrderAmount);
            db.AddInParameter(dbCommand, "ShippingStatus", DbType.String, order.ShippingStatus);
            db.AddInParameter(dbCommand, "PaymentType", DbType.String, order.PaymentType);
            db.AddInParameter(dbCommand, "PaymentTrasectionId", DbType.String, order.PaymentTrasectionId);
            db.AddInParameter(dbCommand, "PaymentStatus", DbType.String, order.PaymentStatus);

            if (db.ExecuteNonQuery(dbCommand) > 0)
                return true;
            return false;
        }

        public static List<Order> GetAll(Boolean allowPaging = false, Int32 pageIndex = 0, Int32 pageSize = 0, String orderColumn = "Id", Boolean isAscending = false)
        {
            List<Order> orderList = new List<Order>();
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Order_GetAll");
            db.AddInParameter(dbCommand, "AllowPaging", DbType.Boolean, allowPaging);
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, pageIndex);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, pageSize);
            db.AddInParameter(dbCommand, "OrderColumn", DbType.String, orderColumn);
            db.AddInParameter(dbCommand, "IsAscending", DbType.Boolean, isAscending);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                orderList = GetEntitiesFromReader(dataReader);
            }
            return orderList;
        }

        public static List<Order> GetByKeys(Int32? id, Int32? userId)
        {
            List<Order> orderList = new List<Order>();
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Order_GetByKeys");
            if (id.HasValue)
                db.AddInParameter(dbCommand, "Id", DbType.Int32, id);
            if (userId.HasValue)
                db.AddInParameter(dbCommand, "UserId", DbType.Int32, userId);

            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                orderList = GetEntitiesFromReader(dataReader);
            }
            return orderList;
        }

        public static bool DeleteByKeys(Int32? id, Int32? userId)
        {
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Order_DeleteByKeys");
            if (id.HasValue)
                db.AddInParameter(dbCommand, "Id", DbType.Int32, id);
            if (userId.HasValue)
                db.AddInParameter(dbCommand, "UserId", DbType.Int32, userId);
            if (db.ExecuteNonQuery(dbCommand) > 0)
                return true;
            return false;
        }

        private static List<Order> GetEntitiesFromReader(IDataReader dataReader)
        {
            List<Order> orderList = new List<Order>();
            while (dataReader.Read())
            {
                orderList.Add(GetEntityFromReader(dataReader));
            }
            return orderList;
        }

        private static Order GetEntityFromReader(IDataReader dataReader)
        {
            Order order = new Order();
            order.Id = GetIntValueFromDB(dataReader["Id"]);
            order.OrderNumber = GetStringValueFromDB(dataReader["OrderNumberPrefix"]);
            order.OrderDate = (DateTime)dataReader["OrderDate"];
            order.UserId = GetIntValueFromDB(dataReader["UserId"]);
            order.UserFullName = GetStringValueFromDB(dataReader["UserFullName"]);
            order.OrderStatus = GetStringValueFromDB(dataReader["OrderStatus"]);
            order.UserEmailAddress = GetStringValueFromDB(dataReader["UserEmailAddress"]);
            order.UserPhoneNumber = GetStringValueFromDB(dataReader["UserPhoneNumber"]);
            order.UserInstruction = GetStringValueFromDB(dataReader["UserInstruction"]);
            order.ShippingName = GetStringValueFromDB(dataReader["ShippingName"]);
            order.ShippingPhoneNumber = GetStringValueFromDB(dataReader["ShippingPhoneNumber"]);
            order.ShippingAddressLine1 = GetStringValueFromDB(dataReader["ShippingAddressLine1"]);
            order.ShippingAddressLine2 = GetStringValueFromDB(dataReader["ShippingAddressLine2"]);
            order.ShippingZip = GetStringValueFromDB(dataReader["ShippingZip"]);
            order.ShippingCity = GetStringValueFromDB(dataReader["ShippingCity"]);
            order.ShippingCountry = GetStringValueFromDB(dataReader["ShippingCountry"]);
            order.ShippingCost = GetDoubleValueFromDB(dataReader["ShippingCost"]);
            order.ShippingStatus = GetStringValueFromDB(dataReader["ShippingStatus"]);
            order.PaymentType = GetStringValueFromDB(dataReader["PaymentType"]);
            order.PaymentTrasectionId = GetStringValueFromDB(dataReader["PaymentTrasectionId"]);
            order.PaymentStatus = GetStringValueFromDB(dataReader["PaymentStatus"]);
            order.SubTotal = GetDoubleValueFromDB(dataReader["SubTotal"]);
            return order;
        }

        public static String GetStringValueFromDB(Object val)
        {
            if (val == System.DBNull.Value)
                return String.Empty;
            else
                return Convert.ToString(val);
        }

        public static Int32 GetIntValueFromDB(Object val)
        {
            if (val == System.DBNull.Value)
                return 0;
            else
                return Convert.ToInt32(val);
        }

        public static Double GetDoubleValueFromDB(Object val)
        {
            if (val == System.DBNull.Value)
                return 0;
            else
                return Convert.ToDouble(val);
        }

        public static List<Order> GetOrdersPaged(string orderStatus,string orderBy, bool isAscending, int pageIndex, int pageSize, int orderNo, DateTime? fromDate, DateTime? toDate)
        {
            List<Order> orderList = new List<Order>();
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Order_GetOrdersPaged");
            db.AddInParameter(dbCommand, "OrderStatus", DbType.String, orderStatus);
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, pageIndex);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, pageSize);
            db.AddInParameter(dbCommand, "OrderColumn", DbType.String, orderBy);
            db.AddInParameter(dbCommand, "IsAscending", DbType.Boolean, isAscending);

            if (orderNo != -1)
            {
                db.AddInParameter(dbCommand, "OrderNo", DbType.Int32, orderNo);
            }
            else
            {
                db.AddInParameter(dbCommand, "OrderNo", DbType.Int32, DBNull.Value);
            }
            
            if (fromDate.HasValue)
                db.AddInParameter(dbCommand, "FromDate", DbType.DateTime, fromDate);
            else
                db.AddInParameter(dbCommand, "FromDate", DbType.DateTime, DBNull.Value);


            if (toDate.HasValue)
                db.AddInParameter(dbCommand, "ToDate", DbType.DateTime, toDate);
            else
                db.AddInParameter(dbCommand, "ToDate", DbType.DateTime, DBNull.Value);

            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                orderList = GetEntitiesFromReader(dataReader);
            }
            return orderList;
        }

        public static Int32 GetOrdersPagedCount(string orderStatus, int orderNo, DateTime? fromDate, DateTime? toDate)
        {
            Int32 totalCount = 0;
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Order_GetOrdersPagedCount");
            db.AddParameter(dbCommand, "ReturnVal", DbType.Int32,ParameterDirection.ReturnValue,"totalCount",DataRowVersion.Default,null);
            
            db.AddInParameter(dbCommand, "OrderStatus", DbType.String, orderStatus);
            if (orderNo != -1)
            {
                db.AddInParameter(dbCommand, "OrderNo", DbType.Int32, orderNo);
            }
            else
            {
                db.AddInParameter(dbCommand, "OrderNo", DbType.Int32, DBNull.Value);
            }

            if (fromDate.HasValue)
                db.AddInParameter(dbCommand, "FromDate", DbType.DateTime, fromDate);
            else
                db.AddInParameter(dbCommand, "FromDate", DbType.DateTime, DBNull.Value);


            if (toDate.HasValue)
                db.AddInParameter(dbCommand, "ToDate", DbType.DateTime, toDate);
            else
                db.AddInParameter(dbCommand, "ToDate", DbType.DateTime, DBNull.Value);

            if (db.ExecuteNonQuery(dbCommand) > 0)
            {
                totalCount = Convert.ToInt32(db.GetParameterValue(dbCommand, "@ReturnVal"));
                
            }

            return totalCount;
        }
    }
}

